if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFdsNStartFeed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFdsNStartFeed]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/***************************************************************************
* Name:			spFdsNStartFeed
* Purpose:		Used to start an instance of a feed type
*
*
* PARAMETERS
* Name					Description					
* -------------			-----------------------------------------------------------
* @_DTSPackageName		Input
* @_FeedInstanceId		Output
* @_DateLastSuccess		Output	
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
*	0				Succeeded
*	1				Failed
***************************************************************************/

CREATE PROCEDURE dbo.spFdsNStartFeed (
	@_DTSPackageName varchar(50)
,	@_FeedInstanceId integer output
,	@_DateLastSuccess datetime output
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @FEED_SUCCESS	Integer
	DECLARE @FEED_INPROCESS Integer
	Set @FEED_SUCCESS = 2
	Set @FEED_INPROCESS = 1

	DECLARE @_myFeedTypeId Integer

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	Get Feed type
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	select
		@_myFeedTypeId = [Id]
	from
		SvcFeedType
	where
		[Name] = @_DTSPackageName

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	If no Feed type, stop here and return nulls!
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	if @_myFeedTypeId is null
	Begin
		Set @_FeedInstanceId = null
		Set @_DateLastSuccess = null
		return 1
	End

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	Get the date of the last successful run!
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	Set @_DateLastSuccess = COALESCE(  (Select
											S.StartDate
										From
											SvcFeedRun S
										Where
											S.SvcFeedTypeId = @_myFeedTypeId
											and S.SvcFeedStatusTypeId = @FEED_SUCCESS
											and S.StartDate =	(Select top 1
																	Max(S1.StartDate)
															 	From
																	SvcFeedRun S1
																Where
																	S1.SvcFeedTypeId = @_myFeedTypeId
																	and S1.SvcFeedStatusTypeId = @FEED_SUCCESS))
									, '1/1/1900')

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	Create a new feed instance and return the instance Id!
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	Insert into SvcFeedRun	(SvcFeedTypeId, SvcFeedStatusTypeId, StartDate, EndDate)
		Values				(@_myFeedTypeId, @FEED_INPROCESS, getdate(), null)

	Select @_FeedInstanceId = SCOPE_IDENTITY()

	SET NOCOUNT OFF

	RETURN 0

END
GO

/******************************************************************
* CHANGE HISTORY
* $History: spFdsNStartFeed.sql $
-- 
*
******************************************************************/

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

